﻿Public Class SubelerdekiAraclar
    Dim dtSubeler As DataTable
    Dim dtAraclar As DataTable
    Dim dtAraclarSay As DataTable
    Dim dtMusaitAraclar As DataTable

    Private Sub SubelerdekiAraclar_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        VerilerlGuncelle()
    End Sub

    Sub VerilerlGuncelle()
        dtSubeler = Sorgula("select * from subeler")
        dtAraclarSay = Sorgula("select subeler.subeID, subeler.subeAdi, COUNT(araclar.sube) as say from subeler " &
                 " left join araclar on (subeler.subeID = araclar.sube) " &
                 " group by subeler.subeID, subeler.subeAdi")
        dtAraclar = Sorgula("select * from araclar")
        dtMusaitAraclar = Sorgula("select * from araclar where sube is null")
        
        
        lbMusaitAraclar.DataSource = dtMusaitAraclar
        lbMusaitAraclar.ValueMember = "aracID"
        lbMusaitAraclar.DisplayMember = "aracAdi"

        'For Each sube In dtAraclarSay.Rows
        '    Try
        '        lbSubeler.Items.Add(New With {.subeID = sube("subeID"), .subeAdi = sube("subeID") & " " & sube("say")})
        '    Catch ex As Exception
        '        MsgBox(ex.Message)
        '    End Try

        'Next
        ' dtSubeler.Columns.Add("tamAd", GetType(String), "subeAdi + say")
        Try
            dtAraclarSay.Columns.Add("tamAd", GetType(String), "trim([subeAdi]) + ' (' + [say] + ')'") ' bu satır 2 saat 12 dk uğraştırdı. trim en az 1 saat uğraştırmıştır ?
            lbSubeler.DisplayMember = "tamad"
            lbSubeler.ValueMember = "subeID"
            lbSubeler.DataSource = dtAraclarSay

        Catch ex As Exception
            MsgBox(ex.Message)
        End Try
        
    End Sub

    Private Sub lbSubeler_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles lbSubeler.SelectedIndexChanged
        Dim subedekiAraclar
        Dim aracbul = From arac In dtAraclar _
                        Where Not IsDBNull(arac("sube")) AndAlso arac("sube") = lbSubeler.SelectedValue
                        Select arac

        If aracbul.Count > 0 Then
            subedekiAraclar = aracbul.CopyToDataTable
            lbSubedekiAraclar.Visible = True
            lbAracYok.Visible = False
            btnSubedenAracCek.Visible = True
        Else
            lbSubedekiAraclar.DataSource = Nothing
            lbSubedekiAraclar.Items.Clear()
            lbSubedekiAraclar.Visible = False
            lbAracYok.Visible = True
            btnSubedenAracCek.Visible = False
            Return
        End If

        lbSubedekiAraclar.DataSource = subedekiAraclar
        lbSubedekiAraclar.DisplayMember = "aracAdi"
        lbSubedekiAraclar.ValueMember = "aracID"

    End Sub

    Private Sub btnSubedenAracCek_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSubedenAracCek.Click
        Dim cekilecekArac = lbSubedekiAraclar.SelectedValue

        Sorgula("update araclar set sube = NULL where aracID = " & cekilecekArac)

        VerilerlGuncelle()
    End Sub

    Private Sub btnAraciSubeyeEkle_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnAraciSubeyeEkle.Click
        Dim atanacakArac = lbMusaitAraclar.SelectedValue
        Dim atanacakSube = lbSubeler.SelectedValue

        Sorgula("update araclar set sube = " & atanacakSube & " where aracID = " & atanacakArac)

        VerilerlGuncelle()
    End Sub
End Class